/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package point.of.sale.app;

import java.sql.*;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;


/**
 *
 * @author card3r
 */
public class SQLHandler {
    public Connection conexiune = null;
    private String server,username,passwd,dbase;
    String executionPath = System.getProperty("user.dir");
    /*----------------------CAUTARE_MYSQL---------------------------*/
    public ResultSet searchMysql(String argNume,String argPrenume,String argTel) throws SQLException
    {
        String qry = "SELECT * FROM clienti WHERE nume LIKE '%" +argNume+ "%' AND prenume LIKE '%"
                        +argPrenume+ "%' AND telefon LIKE '%" +argTel + "%'";
        return this.genericSelect(qry);

    }
    /*------------------INITIALIZARE PARAMETRI MYSQL---------------------*/
    private void initMySQL_params() throws ClassNotFoundException, SQLException
    {
            executionPath = System.getProperty("user.dir");
            //System.out.print("Executing at =>"+executionPath);
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:"+ executionPath + "//config//settings");
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select * from settings");
            while(rs.next())
            {
                server = rs.getString("server");
                username = rs.getString("user");
                passwd = rs.getString("pass");
                dbase = rs.getString("db");
            }
            conn.close();
    }
    /*--------------------INITIALIZARE CONEXIUNE MYSQL--------------------*/
    private void initMySQL_conn() throws ClassNotFoundException, SQLException
    {
        Class.forName("com.mysql.jdbc.Driver");
        String connectionUrl = "jdbc:mysql://" + this.server +"/" + this.dbase +"?user=" + this.username + "&password=" + this.passwd;
        this.conexiune = DriverManager.getConnection(connectionUrl);
    }

    public ResultSet genericSelect(String argGenericSelect) throws SQLException
    {
        Statement stmnt = this.conexiune.createStatement();
        ResultSet rs = stmnt.executeQuery(argGenericSelect);
        return rs;
    }
    /*----------------------------C_TOR----------------------------------*/
    @SuppressWarnings("static-access")
    public SQLHandler()
    {
        try {
            ///////////////////
            initMySQL_params();
            initMySQL_conn();
            ///////////////////
        } catch (ClassNotFoundException ex) {
            JOptionPane x = new JOptionPane();
            x.showMessageDialog(null, ex.toString());
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            Setari.mainSetari();
            JOptionPane x = new JOptionPane();
            x.showMessageDialog(null, ex.toString());
        }
    }
    /*------------------GENERIC INSERT------------------------*/
    public void clientInsert(String nume, String prenume, String telefon, String adresa) throws SQLException
    {
        PreparedStatement ps = this.conexiune.prepareStatement("INSERT INTO clienti(id_client,nume,prenume,adresa,telefon) VALUES (?,?,?,?,?)");
        ps.setString(1, null);
        ps.setString(2, nume);
        ps.setString(3,prenume);
        ps.setString(4,adresa);
        ps.setString(5,telefon);
        ps.executeUpdate();
    }
    public void orderInsert(Vector<Integer> idProd ,Vector<Integer> qty,int  offSet) throws SQLException
    {
        int lastClientID = 0;
        int lastOrderID = 0;
        ResultSet myRs = this.genericSelect("SELECT MAX(id_client) from clienti");
        while (myRs.next()) {
                lastClientID = myRs.getInt("MAX(id_client)");
        }
        /*-----------------------------------------------------------------*/
        java.util.Date today = new java.util.Date();
        java.sql.Timestamp ts1 = new java.sql.Timestamp(today.getTime());
        PreparedStatement ps = this.conexiune.prepareStatement("INSERT INTO comanda(id_comanda,id_client,data,este_livrata,cine) VALUES (?,?,?,?,?)");
        ps.setString(1, null);
        ps.setInt(2, lastClientID);
        ps.setTimestamp(3,ts1);
        ps.setInt(4,0);
        ps.setString(5,"nimeni");
        ps.executeUpdate();
        /*___________________________________________________________________*/
        Statement stmnt = this.conexiune.createStatement();
        stmnt.executeQuery("SELECT MAX(id_comanda) from comanda");
        ResultSet rs= stmnt.getResultSet();
        while(rs.next())
        {
            lastOrderID=rs.getInt("MAX(id_comanda)");
        }
        for(int i=0;i<offSet;i++)
        {
            PreparedStatement ps2= this.conexiune.prepareStatement("INSERT INTO continut_comanda(id_cc,id_produs,qty,id_comanda) VALUES (?,?,?,?)");
            ps2.setString(1, null);
            ps2.setInt(2,idProd.elementAt(i));
            ps2.setInt(3,qty.elementAt(i));
            ps2.setInt(4,lastOrderID);
            ps2.executeUpdate();
        }
    }
    public void genericDelete(int argIdToDelete,String argColumnIdName, String argTableName)
    {
        try {
            Statement stmnt = this.conexiune.createStatement();
            stmnt.execute("DELETE FROM "+argTableName+" WHERE "+argColumnIdName+" = "+argIdToDelete);
            stmnt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
        }
    }
    public String getYear()
    {
        java.util.Date today = new java.util.Date();
        java.sql.Timestamp ts1 = new java.sql.Timestamp(today.getTime());
        return ts1.toString().substring(0, 4);
    }
    public String getMonth()
    {
        java.util.Date today = new java.util.Date();
        java.sql.Timestamp ts1 = new java.sql.Timestamp(today.getTime());
        return ts1.toString().substring(5, 7);
    }
    public String getDay()
    {
        java.util.Date today = new java.util.Date();
        java.sql.Timestamp ts1 = new java.sql.Timestamp(today.getTime());
        return ts1.toString().substring(8, 10);
    }
    public String getFullDate()
    {
        java.util.Date today = new java.util.Date();
        java.sql.Timestamp ts1 = new java.sql.Timestamp(today.getTime());
        return ts1.toString().substring(0, 10);
    }
    public String getNumeProdus(int argId)
    {
        try {
            String numeProdus = null;
            ResultSet rs = this.genericSelect("SELECT * FROM produs WHERE id_produs= " + argId);
            while(rs.next())
            {
                numeProdus = rs.getString("nume");
            }
            return numeProdus;
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
            return null;
        }
    }
    public Float getPretProdus(int argId)
    {
        try {
            Float pretProdus = 0f;
            ResultSet rs = this.genericSelect("SELECT * FROM produs WHERE id_produs= " + argId);
            while(rs.next())
            {
                pretProdus = rs.getFloat("pret");
            }
            return pretProdus;
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
            return 0f;
        }
    }
    public String getContinutProdus(int argId)
    {
        try {
            String continutProdus = null;
            ResultSet rs = this.genericSelect("SELECT * FROM produs WHERE id_produs= " + argId);
            while(rs.next())
            {
                continutProdus = rs.getString("continut");
            }
            return continutProdus;
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
            return null;
        }
    }
    public void insertProduct(String nume,Float pret,String continut,String cale_relativa_poza)
    {
        try {
            PreparedStatement ps = this.conexiune.prepareStatement("INSERT INTO produs(id_produs,nume,pret,continut) VALUES (?,?,?,?)");
            ps.setString(1, null);
            ps.setString(2, nume);
            ps.setFloat(3, pret);
            ps.setString(4, continut);
            ps.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
        }

    }
    public void deleteProductById(Integer argId)
    {
        try {
            Statement s = this.conexiune.createStatement();
            s.execute("DELETE FROM produs WHERE id_produs = " + argId);
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
        }
    }
    public Integer getLastInsertedProduct()
    {
        try {
            int retVal = 0;
            ResultSet rs = this.genericSelect("SELECT MAX(id_produs) FROM produs");
            while (rs.next())
            {
                retVal = rs.getInt("MAX(id_produs)");
            }
            return retVal;
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
            return 0;
        }

    }
    public void insert_Image_Path(Integer id,String filename)
    {
        try {
            PreparedStatement ps = this.conexiune.prepareStatement("INSERT INTO poza(id_poza,id_produs,cale_relativa)VALUES (?,?,?)");
            ps.setString(1, null);
            ps.setInt(2, id);
            ps.setString(3, filename);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
        }
    }
    public String getCalePoza(int argIdProdus)
    {
        try {
            String cale = null;
            ResultSet rs = this.genericSelect("SELECT * FROM poza WHERE id_produs= " + argIdProdus);
            while (rs.next()) {
                cale=rs.getString("cale_relativa");
                
            }
            String caleFinala =this.executionPath+"/img/"+cale;

            
            return caleFinala;
        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
            return null;
        }
    }
    public void updateComanda(int argId,String argCine)
    {
        try {
            PreparedStatement ps = this.conexiune.prepareStatement("UPDATE comanda SET cine = '" + argCine + "', este_livrata = '1' WHERE id_comanda = " + argId);
            ps.executeUpdate();
            ps.close();

        } catch (SQLException ex) {
            Logger.getLogger(SQLHandler.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.toString());
        }
    }
}